﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.Sql;
using System.Data.SqlClient;
using System.IO;

namespace ScriptExecutionV2
{
    public partial class _Default : System.Web.UI.Page
    {

        private SqlConnection cnn;
        private SqlCommand cmd;
        private string report;

        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void btnExecutar_Click(object sender, EventArgs e)
        {
            if (txtInput.Text == "" || chkArtemis.Checked && chkMenelau.Checked && chkFebe.Checked && chkAriadne.Checked && chkIxiao.Checked == false)
            {
                pnResults.Visible = false;

                lblReport.Visible = true;

                lblReport.Text = "<b>Seleccione o(s) Servidor(es) e Base(s) de Dados!</b>";
            }
            else
            {
                lblReport.Text = "";
            }

            if (chkFebe.Checked == true)
            {
                runQuery("FEBE", ddlFebe.SelectedItem.Text, txtInput.Text);

                lblReport.Visible = true;

                lblReport.Text += report;
            }

            if (chkAriadne.Checked == true)
            {
                runQuery("ARIADNE", ddlAriadne.SelectedItem.Text, txtInput.Text);

                lblReport.Visible = true;

                lblReport.Text = report + lblReport.Text;
            }

            if (chkIxiao.Checked == true)
            {
                runQuery("IXIAO", ddlIxiao.SelectedItem.Text, txtInput.Text);

                lblReport.Visible = true;

                lblReport.Text = report + lblReport.Text;
            }

            if (chkMenelau.Checked == true)
            {
                runQuery("MENELAU", ddlMenelau.SelectedItem.Text, txtInput.Text);

                lblReport.Visible = true;

                lblReport.Text = report + lblReport.Text;
            }

            if (chkArtemis.Checked == true)
            {
                runQuery("ARTEMIS", ddlArtemis.SelectedItem.Text, txtInput.Text);

                lblReport.Visible = true;

                lblReport.Text = report + lblReport.Text;
            }
        }

        protected void btnImportar_Click(object sender, EventArgs e)
        {
            pnResults.Visible = false;

            gvResults.Visible = false;

            StreamReader sr = null;

            try
            {
                sr = new StreamReader(fupImportScript.PostedFile.FileName);

                txtInput.Text = sr.ReadToEnd();

                report = "<b>Importação concluída com sucesso!</b><br />";
            }

            catch (Exception ex)
            {

                report = "<b>Erro:</b> Ocorreu uma falha na importação: " + ex.Message.ToString() + "<br />";

            }

            finally
            {

                if (sr != null)
                {
                    sr.Close();
                }

            }

            lblReport.Visible = true;

            lblReport.Text = report;
        }

        public void runQuery(string servidor, string baseDados, string query)
        {
            string totalScript = " USE " + baseDados + " " + query;

            cnn = new SqlConnection("Data Source=" + servidor + ";Database=" + baseDados + ";" + getUserPass(servidor));

            cmd = new SqlCommand("IDH.DBO.SCR_RUN_QUERY", cnn);

            cmd.Parameters.Add(new SqlParameter("@Query", totalScript));

            cmd.CommandType = CommandType.StoredProcedure;

            SqlDataAdapter da = new SqlDataAdapter(cmd);

            if (query == "")
            {
                lblReport.Visible = true;

                lblReport.Text = "<b>Não existe nada para executar!</b>";
            }
            else if (txtInput.Text.StartsWith("SELECT") || txtInput.Text.StartsWith("select"))
            {
                try
                {

                    DataTable dt = new DataTable();

                    da.Fill(dt);
                    {

                        gvResults.DataSource = dt;

                        gvResults.DataBind();

                    }

                    pnResults.Visible = true;

                    gvResults.Visible = true;

                    report = "<b>" + servidor + ":</b> O script correu com sucesso! <br />";

                }
                catch (Exception ex)
                {
                    report = "<b>" + servidor + ":</b> O script devolveu um erro: " + ex.Message.ToString() + "<br />";
                }
                finally
                {
                    cnn.Close();
                }
            }
            else
            {
                pnResults.Visible = false;

                if (cnn.State != ConnectionState.Open)
                {
                    cnn.Open();
                }

                else
                {
                    throw new Exception("A conexao ja estava aberta: " + cnn);
                }

                try
                {
                    cmd.ExecuteNonQuery();

                    report = "<b>" + servidor + ":</b> O script correu com sucesso! <br />";
                }

                catch (Exception ex)
                {
                    report = "<b>" + servidor + ":</b> O script devolveu um erro: " + ex.Message.ToString() + "<br />";
                }

                finally
                {
                    cnn.Close();
                }

            }

        }

        protected void chkFebe_CheckedChanged(object sender, EventArgs e)
        {
            if (chkFebe.Checked == true)
            {
                ddlFebe.Enabled = true;
            }
            else
            {
                ddlFebe.Enabled = false;
            }
        }

        protected void chkAriadne_CheckedChanged(object sender, EventArgs e)
        {
            if (chkAriadne.Checked == true)
            {
                ddlAriadne.Enabled = true;
            }
            else
            {
                ddlAriadne.Enabled = false;
            }
        }

        protected void chkMenelau_CheckedChanged(object sender, EventArgs e)
        {
            if (chkMenelau.Checked == true)
            {
                ddlMenelau.Enabled = true;
            }
            else
            {
                ddlMenelau.Enabled = false;
            }
        }

        protected void chkIxiao_CheckedChanged(object sender, EventArgs e)
        {
            if (chkIxiao.Checked == true)
            {
                ddlIxiao.Enabled = true;
            }
            else
            {
                ddlIxiao.Enabled = false;
            }
        }

        protected void chkArtemis_CheckedChanged(object sender, EventArgs e)
        {
            if (chkArtemis.Checked == true)
            {
                ddlArtemis.Enabled = true;
            }
            else
            {
                ddlArtemis.Enabled = false;
            }
        }

        //UserName e Password para cada Servidor.
        private string cnnMenelau
        {
            get
            {
                return "User ID=sa; Password=infante;";
            }
        }

        private string cnnArtemis
        {
            get
            {
                return "User ID=sa; Password=dragonballZ;";
            }
        }

        private string cnnAriadne
        {
            get
            {
                return "User ID=sa; Password=infante;";
            }
        }

        private string cnnIxiao
        {
            get
            {
                return "User ID=sa; Password=infante;";
            }
        }

        private string cnnFebe
        {
            get
            {
                return "User ID=sa; Password=infante;";
            }
        }

        //Método que retorna o username e password do servidor escolhido.
        public string getUserPass(string servidor)
        {
            string userPass;

            switch (servidor.ToLower())
            {
                case "menelau":
                    userPass = cnnMenelau;
                    break;
                case "artemis":
                    userPass = cnnArtemis;
                    break;
                case "febe":
                    userPass = cnnFebe;
                    break;
                case "ixiao":
                    userPass = cnnIxiao;
                    break;
                case "ariadne":
                    userPass = cnnAriadne;
                    break;
                default:
                    userPass = cnnFebe;
                    break;
            }

            return userPass;
        }

        //protected void Button1_Click(object sender, EventArgs e)
        //{
        //    
        //    pnResults.Visible = true;
        //    gvResults.Visible = true;
        //    querySelect("FEBE", ddlFebe.SelectedItem.Text, txtInput.Text);
        //    lblReport.Visible = true;
        //    lblReport.Text += report;
        //}

        
        //public void querySelect(string servidor, string baseDados, string query)
        //{
        //    string totalScript = " USE " + baseDados + " " + query;

        //    cnn = new SqlConnection("Data Source=" + servidor + ";Database=" + baseDados + ";" + getUserPass(servidor));

        //    cmd = new SqlCommand("IDH.DBO.SCR_RUN_QUERY", cnn);

        //    cmd.Parameters.Add(new SqlParameter("@Query", totalScript));

        //    cmd.CommandType = CommandType.StoredProcedure;

        //    SqlDataAdapter da = new SqlDataAdapter(cmd);

        //    if (query == "")
        //    {
        //        lblReport.Visible = true;

        //        lblReport.Text = "<b>Não existe nada para executar!</b>";
        //    }
        //    else if (txtInput.Text.StartsWith("SELECT") || txtInput.Text.StartsWith("select"))
        //    {
        //        try
        //        {

        //            DataTable dt = new DataTable();

        //            da.Fill(dt);
        //            {

        //                gvResults.DataSource = dt;

        //                gvResults.DataBind();

        //            }
        //            report = "<b>" + servidor + ":</b> O script correu com sucesso! <br />";

        //        }
        //        catch (Exception ex)
        //        {
        //            report = "<b>" + servidor + ":</b> O script devolveu um erro: " + ex.Message.ToString() + "<br />";
        //        }
        //        finally
        //        {
        //            cnn.Close();
        //        }
        //    }
        //    else
        //    {
        //        if (cnn.State != ConnectionState.Open)
        //        {
        //            cnn.Open();
        //        }

        //        else
        //        {
        //            throw new Exception("A conexao ja estava aberta: " + cnn);
        //        }

        //        try
        //        {
        //            cmd.ExecuteNonQuery();

        //            report = "<b>" + servidor + ":</b> O script correu com sucesso! <br />";
        //        }

        //        catch (Exception ex)
        //        {
        //            report = "<b>" + servidor + ":</b> O script devolveu um erro: " + ex.Message.ToString() + "<br />";
        //        }

        //        finally
        //        {
        //            cnn.Close();
        //        }
 
        //    }

        //}

    }
}
